﻿using System;
using System.Collections.Generic;
using System.Linq;

using Dapper;

using aier.dataaccess.model;
using log4net;

namespace aier.dataaccess.dapper
{
    public class ScreenDal
    {
        public ILog log { get; set; }
        public DapperHelper dapperHelper { get; set; }

        #region Query查询数据

        /// <summary>
        /// 根据id查询筛查信息
        /// 原程序方法：ScreeningServiceImpl-> findScInfoById(String scid) 
        /// </summary>
        /// <param name="screenId">筛查信息id</param>
        /// <returns>单条筛查信息</returns>
        public ScreenView FetchScreenByScreenID(Guid screenId)
        {

            return dapperHelper.ExecueWithoutTransaction<ScreenView>(a =>
            {
                string sql = @"select * from AIER_SCREEN_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_screenId = @screenId", new { screenId = screenId });
                //sqlBuilder.AddParameters(new { siteid= hospitalId });
                return a.Query<ScreenView>(sqlTemplate.RawSql, sqlTemplate.Parameters).First();
            });
        }

        /// <summary>
        /// 获取所有筛查活动
        /// 原程序方法 ：ScreeningServiceImpl-> findAllScInfos   
        /// </summary>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面显示条数</param>
        /// <returns></returns>
        public IEnumerable<ScreenView> GetAllScreen(int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * pageSize;

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenView>>(a =>
            {
                string sql = $@"select * from AIER_SCREEN_INFO order by new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} ROWS ONLY";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<ScreenView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl-> findScpqByUsername(String username)
        /// <param name="domainname">域账号</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenByDomainName(string domainname)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                string sql = $@" select * from
                                (
                                    select* from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0
                                    union
                                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                                ) t ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl->findScpqByUsername(String username, int fromIndex, int toIndex)
        /// <param name="domainname">域账号</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面显示条数</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenByDomainName(string domainname, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize-1);

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                //string sql = $@" select * from
                //                (
                //                    select* from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0
                //                    union
                //                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                //                ) t order by t.new_screenId desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";


                string sql = $@" select * from
                                (
                                    select * from AIER_T_SC_PQ t where exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    )
                                    union
                                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                                ) t order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl->findScpqByUsername(String username, int fromIndex, int toIndex)
        /// <param name="domainname">域账号</param>
        /// <returns></returns>
        public int GetCountScreenByDomainName(string domainname)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                //string sql = $@"select count(1) from
                //                (
                //                    select t.new_screenId from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0
                //                    union
                //                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                //                ) as t";

                string sql = $@"select count(1) from
                                (
                                    select t.new_screenId from AIER_T_SC_PQ t where exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    )
                                    union
                                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                                ) as t";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }
        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl->findScpqByUsername(String username, int fromIndex, int toIndex)
        /// <param name="domainname">域账号</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面显示条数</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenByDomainName1(string domainname, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize - 1);

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                //string sql = $@" select * from
                //                (
                //                    select* from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0
                //                    union
                //                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                //                ) t order by t.new_screenId desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";


                string sql = $@" select * from
                                (
                                    select * from AIER_T_SC_PQ t where exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    ) and t.new_screentype!=100000001
                                    union
                                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}' and t.new_screentype!=100000001
                                ) t order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl->findScpqByUsername(String username, int fromIndex, int toIndex)
        /// <param name="domainname">域账号</param>
        /// <returns></returns>
        public int GetCountScreenByDomainName1(string domainname)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                //string sql = $@"select count(1) from
                //                (
                //                    select t.new_screenId from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0
                //                    union
                //                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}'
                //                ) as t";

                string sql = $@"select count(1) from
                                (
                                    select t.new_screenId from AIER_T_SC_PQ t where exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    ) and t.new_screentype!=100000001
                                    union
                                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and t.new_screentype!=100000001
                                ) as t";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.ExecuteScalar<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表(不包括大客户和大学校园筛查)
        /// 原程序方法：ScreeningServiceImpl-> findScpqNoBigByUsername(String username, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面显示条数</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenNoBigDomainName(string domainname, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize - 1);

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                string sql = $@" select * from
                                 (
                                    select * from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0  and t.new_screentype in ('100000000','100000001')
                                    union
                                    select * from AIER_T_SC_PQ t where OwnerName = '{domainname}' and t.new_screentype in ('100000000','100000001')
                                 ) t  order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }


        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表(不包括大客户和大学校园筛查)
        /// 原程序方法：ScreeningServiceImpl-> findScpqNoBigByUsername(String username, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面显示条数</param>
        /// <returns></returns>
        public int GetCountScreenNoBigDomainName(string domainname)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                string sql = $@" select count(1) from
                                 (
                                    select t.new_screenId from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0  and t.new_screentype in ('100000000','100000001')
                                    union
                                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and t.new_screentype in ('100000000','100000001')
                                 ) t  ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirst<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl-> findScpqByUsername(String username, String scState) 
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="scState">筛查状态</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenByDomainNameAndState(string domainname, string scState)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                string sql = $@"select * from
                                (
                                    select* from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}
                                    union
                                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                                ) t ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl-> findScpqByUsername(String username, String scState, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="scState">筛查状态</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面大小</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenByDomainNameAndState(string domainname, string scState, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize-1);

       

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                //string sql = $@" select * from
                //                 (
                //                    select * from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}
                //                    union
                //                    select * from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                //                 ) t  order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";

                string sql = $@" select * from
                                 (
                                    select * from AIER_T_SC_PQ t where  exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    ) and new_screenstate = {scState}
                                    union
                                    select * from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                                 ) t  order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";


                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }


        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl-> findScpqByUsername(String username, String scState, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="scState">筛查状态</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面大小</param>
        /// <returns></returns>
        public int GetCountScreenByDomainNameAndState(string domainname, string scState)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                //string sql = $@" select Count(1) from
                //                 (
                //                    select t.new_screenId from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}
                //                    union
                //                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                //                 ) t ";

                string sql = $@" select Count(1) from
                                 (
                                    select t.new_screenId from AIER_T_SC_PQ t where  exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    )  and new_screenstate = {scState}
                                    union
                                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                                 ) t ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirst<int>(sqlTemplate.RawSql);
            });

        }
        public IEnumerable<Screen_PQView> GetScreenByDomainNameAndState1(string domainname, string scState, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize - 1);



            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                //string sql = $@" select * from
                //                 (
                //                    select * from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}
                //                    union
                //                    select * from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                //                 ) t  order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";

                string sql = $@" select * from
                                 (
                                    select * from AIER_T_SC_PQ t where  exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    ) and new_screenstate = {scState} and new_screentype!=100000001
                                    union
                                    select * from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState} and new_screentype!=100000001
                                 ) t  order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";


                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }


        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表,负责人或组员里包含登录用户
        /// 原程序方法：ScreeningServiceImpl-> findScpqByUsername(String username, String scState, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="scState">筛查状态</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面大小</param>
        /// <returns></returns>
        public int GetCountScreenByDomainNameAndState1(string domainname, string scState)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                //string sql = $@" select Count(1) from
                //                 (
                //                    select t.new_screenId from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}
                //                    union
                //                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState}
                //                 ) t ";

                string sql = $@" select Count(1) from
                                 (
                                    select t.new_screenId from AIER_T_SC_PQ t where  exists (
                                      select su.DomainName from new_new_screen_systemuser sysuser 
	                                  left join SystemUser su on su.SystemUserId = sysuser.systemuserid 
	                                  where sysuser.new_screenid = t.new_screenId and su.DomainName='{domainname}'
                                    )  and new_screenstate = {scState} and new_screentype!=100000001
                                    union
                                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState} and new_screentype!=100000001
                                 ) t ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirst<int>(sqlTemplate.RawSql);
            });

        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表(不包括大客户和大学校园筛查)
        /// 原程序方法：ScreeningServiceImpl-> findScpqNoBigByUsername(String username, String scState, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="scState">筛查状态</param>
        /// <param name="pageIndex">当前页索引</param>
        /// <param name="pageSize">页面大小</param>
        /// <returns></returns>
        public IEnumerable<Screen_PQView> GetScreenNoBigDomainNameAndState(string domainname, string scState, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize-1);

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_PQView>>(a =>
            {
                string sql = $@" select * from
                                 (
                                    select* from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}  and t.new_screentype in ('100000000','100000001')
                                    union
                                    select* from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState} and t.new_screentype in ('100000000','100000001')
                                 ) t  order by t.new_startdate desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_PQView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 分页查询当前用户相关的筛查排期列表(不包括大客户和大学校园筛查)
        /// 原程序方法：ScreeningServiceImpl-> findScpqNoBigByUsername(String username, String scState, int fromIndex, int toIndex)
        /// </summary>
        /// <param name="domainname">域账号</param>
        /// <param name="scState">筛查状态</param>
        /// <returns></returns>
        public int GetCountScreenNoBigDomainNameAndState(string domainname, string scState)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                string sql = $@" select count(1) from
                                 (
                                    select t.new_screenId from AIER_T_SC_PQ t where CHARINDEX('{domainname}',DomainName) > 0 and new_screenstate = {scState}  and t.new_screentype in ('100000000','100000001')
                                    union
                                    select t.new_screenId from AIER_T_SC_PQ t where OwnerName = '{domainname}' and new_screenstate = {scState} and t.new_screentype in ('100000000','100000001')
                                 ) t  ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirst<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据客户ID查询该客户所有参与的筛查信息
        /// 原程序方法：ScreeningServiceImpl-> findScInfosByCustomerId(String customerId)  ,findScInfosByLeadId(String leadId)
        /// </summary>
        /// <param name="customerid">客户ID</param>
        /// <returns></returns>
        public IEnumerable<Screen_Info_For_AccountView> GetScreenByCustomerId(Guid customerid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Screen_Info_For_AccountView>>(a =>
            {
                string sql = $@" select * from AIER_SCREEN_INFO_FOR_ACCOUNT where leadid='{customerid}' ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Screen_Info_For_AccountView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据姓名模糊查询CRM客户信息
        /// 原程序方法：ScreeningServiceImpl-> selectAierAccountCustomerinfos(DoctorUser user, String key)
        /// </summary>
        /// <param name="userInfo">用户信息</param>
        /// <param name="fullname">姓名</param>
        /// <returns></returns>
        public IEnumerable<Account_CustomerInfoView> GetAccountCustomerInfoByFullName(UserInfo userInfo,string fullname, int pageIndex, int pageSize)
        {
            //需要跳过的记录数
            int skipIndex = (pageIndex - 1) * (pageSize - 1);

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Account_CustomerInfoView>>(a =>
            {
                string sql = string.Empty;
                if (userInfo.userid != null)
                {
                    sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and ownerid='{userInfo.userid}' and charindex('{fullname}',FullName)>0 order by createdon desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";
                }
                else
                {
                    sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and charindex('{fullname}',FullName)>0 order by createdon desc OFFSET {skipIndex} ROW FETCH NEXT {pageSize} rows only";
                }

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据姓名模糊查询CRM客户信息
        /// 原程序方法：ScreeningServiceImpl-> selectAierAccountCustomerinfos(DoctorUser user, String key)
        /// </summary>
        /// <param name="userInfo">用户信息</param>
        /// <param name="fullname">姓名</param>
        /// <returns></returns>
        public int GetCountAccountCustomerInfoByFullName(UserInfo userInfo, string fullname)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                string sql = string.Empty;
                if (userInfo.userid != null)
                {
                    sql = $@" select count(1) from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and ownerid='{userInfo.userid}' and charindex('{fullname}',FullName)>0 ";
                }
                else
                {
                    sql = $@" select count(1) from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and charindex('{fullname}',FullName)>0 ";
                }

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirst<int>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据手机号模糊查询CRM客户信息
        /// 原程序方法：ScreeningServiceImpl-> selectAierAccountCustomerinfos(DoctorUser user, String key)
        /// </summary>
        /// <param name="userInfo">用户信息</param>
        /// <param name="phone">手机号</param>
        /// <returns></returns>
        public IEnumerable<Account_CustomerInfoView> GetAccountCustomerInfoByPhone(UserInfo userInfo, string phone)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Account_CustomerInfoView>>(a =>
            {
                string sql = string.Empty;
                if (userInfo.userid != null)
                {
                    sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and ownerid='{userInfo.userid}' and charindex('{phone}',address1_telephone1)>0 ";
                }
                else
                {
                    sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and charindex('{phone}',address1_telephone1)>0";
                }

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据身份证号模糊查询CRM客户信息
        /// 原程序方法：ScreeningServiceImpl-> selectAierAccountCustomerinfos(DoctorUser user, String key)
        /// </summary>
        /// <param name="userInfo">用户信息</param>
        /// <param name="new_citizenid">身份证号</param>
        /// <returns></returns>
        public IEnumerable<Account_CustomerInfoView> GetAccountCustomerInfoByCitizenid(UserInfo userInfo, string new_citizenid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Account_CustomerInfoView>>(a =>
            {
                string sql = string.Empty;
                if (userInfo.userid != null)
                {
                    sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and ownerid='{userInfo.userid}' and charindex('{new_citizenid}',new_citizenid)>0";
                }
                else
                {
                    sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where new_hospital ='{userInfo.siteid}' and charindex('{new_citizenid}',new_citizenid)>0";
                }

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 统计页面查询客户列表
        /// 原程序方法：ScreeningServiceImpl-> statistics4CustomerList(String teamid, String startdate, String enddate, String qryType, String key) 
        /// </summary>
        /// <param name="teamid">筛查组ID</param>
        /// <param name="startdate">预约创建日期</param>
        /// <param name="enddate">预约创建日期</param>
        /// <param name="qryType">查询类型："tohospital"（到院）,"sign"（签约手术）,"operate"（手术）,"notoperate"（非手术）</param>
        /// <param name="fullname">模糊查询</param>
        /// <returns></returns>
        public IEnumerable<Account_CustomerInfoView> GetAccountCustomerInfoByTeamIdAndCreatedOnAndFullname(string teamid, string startdate, string enddate, string qryType, string fullname)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Account_CustomerInfoView>>(a =>
            {
                string whereSql = string.Empty;
               
                if (qryType.Trim().Equals("tohospital"))
                {
                    whereSql = string.Concat(whereSql,  "and new_istohospital= 1 ");
                }
                else if (qryType.Trim().Equals("sign"))
                {
                    whereSql = string.Concat(whereSql, "and new_issign= 1 ");
                }
                else if(qryType.Trim().Equals("operate"))
                {
                    whereSql = string.Concat(whereSql,  "and isoperate= 1 ");
                }
                else if(qryType.Trim().Equals("notoperate"))
                {
                    whereSql = string.Concat(whereSql,  "and isoperate= 0 ");
                }
                if (!string.IsNullOrEmpty(startdate))
                {
                    whereSql = string.Concat(whereSql, string.Format( "and CreatedOn>= '{0}'", startdate));
                }
                if (!string.IsNullOrEmpty(enddate))
                {
                    whereSql = string.Concat(whereSql, string.Format("and CreatedOn<= '{0}'", enddate));
                }
                string sql = string.Format(@"select * 
                                            from dbo.AIER_ACCOUNT_CUSTOMERINFO c 
                                            where 
                                            exists
                                            (
                                                 select new_lead 
	                                             from dbo.new_reservationBase r
	                                             where exists 
	                                             (
		                                            select distinct new_screenId 
		                                            from dbo.AIER_SCREEN_INFO 
		                                            where new_screenteamid='{0}' and new_screenId = r.new_screen
	                                            )
	                                            and c.LeadId = r.new_lead {1}
                                            ) and charindex('{2}',fullname)>0", teamid, whereSql, fullname);

                var sqlTemplate = new SqlBuilder().AddTemplate(sql);
                log.Info(string.Format("ScreenDal 对像中,GetAccountCustomerInfoByTeamIdAndCreatedOnAndFullname 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 统计页面查询客户列表
        /// 原程序方法：ScreeningServiceImpl-> statistics4CustomerList(String teamid, String startdate, String enddate, String qryType, String key) 
        /// </summary>
        /// <param name="teamid">筛查组ID</param>
        /// <param name="startdate">预约创建日期</param>
        /// <param name="enddate">预约创建日期</param>
        /// <param name="qryType">查询类型："tohospital"（到院）,"sign"（签约手术）,"operate"（手术）,"notoperate"（非手术）</param>
        /// <param name="phone">模糊查询</param>
        /// <returns></returns>
        public IEnumerable<Account_CustomerInfoView> GetAccountCustomerInfoByTeamIdAndCreatedOnAndPhone(string teamid, string startdate, string enddate, string qryType, string phone)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Account_CustomerInfoView>>(a =>
            {
                string whereSql = string.Empty;

                if (qryType.Trim().Equals("tohospital"))
                {
                    whereSql = string.Concat(whereSql, "and new_istohospital= 1 ");
                }
                else if (qryType.Trim().Equals("sign"))
                {
                    whereSql = string.Concat(whereSql, "and new_issign= 1 ");
                }
                else if (qryType.Trim().Equals("operate"))
                {
                    whereSql = string.Concat(whereSql, "and isoperate= 1 ");
                }
                else if (qryType.Trim().Equals("notoperate"))
                {
                    whereSql = string.Concat(whereSql, "and isoperate= 0 ");
                }
                if (!string.IsNullOrEmpty(startdate))
                {
                    whereSql = string.Concat(whereSql, "and CreatedOn>= 1 ");
                }
                if (!string.IsNullOrEmpty(enddate))
                {
                    whereSql = string.Concat(whereSql, "and CreatedOn<= 1 ");
                }

                string sql = string.Format(@"select * 
                                            from dbo.AIER_ACCOUNT_CUSTOMERINFO c 
                                            where 
                                            exists
                                            (
                                                 select new_lead 
	                                             from dbo.new_reservationBase r
	                                             where exists 
	                                             (
		                                            select distinct new_screenId 
		                                            from dbo.AIER_SCREEN_INFO 
		                                            where new_screenteamid='{0}' and new_screenId = r.new_screen
	                                            )
	                                            and c.LeadId = r.new_lead {1}
                                            ) and charindex('{2}',address1_telephone1)>0", teamid, whereSql, phone);

                var sqlTemplate = new SqlBuilder().AddTemplate(sql);
                log.Info(string.Format("ScreenDal 对像中,GetAccountCustomerInfoByTeamIdAndCreatedOnAndPhone 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 统计页面查询客户列表
        /// 原程序方法：ScreeningServiceImpl-> statistics4CustomerList(String teamid, String startdate, String enddate, String qryType, String key) 
        /// </summary>
        /// <param name="teamid">筛查组ID</param>
        /// <param name="startdate">预约创建日期</param>
        /// <param name="enddate">预约创建日期</param>
        /// <param name="qryType">查询类型："tohospital"（到院）,"sign"（签约手术）,"operate"（手术）,"notoperate"（非手术）</param>
        /// <param name="citizenid">模糊查询</param>
        /// <returns></returns>
        public IEnumerable<Account_CustomerInfoView> GetAccountCustomerInfoByTeamIdAndCreatedOnAndCitizenid(string teamid, string startdate, string enddate, string qryType, string citizenid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<Account_CustomerInfoView>>(a =>
            {
                string whereSql = string.Empty;

                if (qryType.Trim().Equals("tohospital"))
                {
                    whereSql = string.Concat(whereSql, "and new_istohospital= 1 ");
                }
                else if (qryType.Trim().Equals("sign"))
                {
                    whereSql = string.Concat(whereSql, "and new_issign= 1 ");
                }
                else if (qryType.Trim().Equals("operate"))
                {
                    whereSql = string.Concat(whereSql, "and isoperate= 1 ");
                }
                else if (qryType.Trim().Equals("notoperate"))
                {
                    whereSql = string.Concat(whereSql, "and isoperate= 0 ");
                }
                if (!string.IsNullOrEmpty(startdate))
                {
                    whereSql = string.Concat(whereSql, "and CreatedOn>= 1 ");
                }
                if (!string.IsNullOrEmpty(enddate))
                {
                    whereSql = string.Concat(whereSql, "and CreatedOn<= 1 ");
                }

                string sql = string.Format(@"select * 
                                            from dbo.AIER_ACCOUNT_CUSTOMERINFO c 
                                            where 
                                            exists
                                            (
                                                 select new_lead 
	                                             from dbo.new_reservationBase r
	                                             where exists 
	                                             (
		                                            select distinct new_screenId 
		                                            from dbo.AIER_SCREEN_INFO 
		                                            where new_screenteamid='{0}' and new_screenId = r.new_screen
	                                            )
	                                            and c.LeadId = r.new_lead {1}
                                            ) and charindex('{2}',new_citizenid)>0", teamid, whereSql, citizenid);

                var sqlTemplate = new SqlBuilder().AddTemplate(sql);
                log.Info(string.Format("ScreenDal 对像中,GetAccountCustomerInfoByTeamIdAndCreatedOnAndCitizenid 方法 ,SQL：'{0}'", sqlTemplate.RawSql));
                return a.Query<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }


        /// <summary>
        /// 根据客户id查询CRM单个客户信息
        /// 原程序方法：ScreeningServiceImpl-> selectAierAccountCustomerinfoByLeadId(String leadid) 
        /// </summary>
        /// <param name="leadid">客户id</param>
        /// <returns></returns>
        public Account_CustomerInfoView GetAccountCustomerInfoByLeadId(Guid leadid)
        {
            return dapperHelper.ExecueWithoutTransaction<Account_CustomerInfoView>(a =>
            {
                string sql = $@" select * from AIER_ACCOUNT_CUSTOMERINFO where leadid ='{leadid}' ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirstOrDefault<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据客户id查询CRM单个客户信息
        /// 原程序方法：ScreeningServiceImpl-> queryCustomerInfoByTaskid(String taskid)
        /// </summary>
        /// <param name="leadid">客户id</param>
        /// <returns></returns>
        public Account_CustomerInfoView GetAccountCustomerInfoByTaskId(Guid taskid)
        {
            return dapperHelper.ExecueWithoutTransaction<Account_CustomerInfoView>(a =>
            {
                string sql = $@" select top 1 * from AIER_ACCOUNT_CUSTOMERINFO  
   	                             where leadid=(select top 1 new_leadid leadid from dbo.AIER_TASK_INFO where taskid= '{taskid}') ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirstOrDefault<Account_CustomerInfoView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        ///  查询大客户活动合作项目
        ///  原程序 ScreeningServiceImpl-> findJointProjectInfo(int projectCategory, int projectPhase)
        /// </summary>
        /// <param name="projectCategory">项目类别</param>
        /// <param name="projectPhase">项目状态</param>
        public IEnumerable<JoinprojectView> QueryJoinProjectByCategoryAndPhase(int projectCategory, int projectPhase)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<JoinprojectView>>(a =>
            {
                string sql = $@"select 
                                new_jointprojectId, new_name, new_account, new_accountName, new_description, new_dline, 
                                new_effectivedate, new_hospital, new_hospitalName, new_maincontact, new_maincontactName, 
                                new_projectabbreviation, new_projectcategory, new_projectphase, new_projectredname 
                                from AIER_JOINTPROJECT_INFO 
                                where new_projectcategory ={projectCategory} and new_projectphase={projectPhase}";
                

                var sqlTemplate = new SqlBuilder().AddTemplate(sql);
                return a.Query<JoinprojectView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 查询筛查检查项目模板
        /// 原程序：ScreeningServiceImpl-> listNewTScPageElement(String name, String ownerid, Integer statecode)
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="ownerid">负责人ID</param>
        /// <param name="statecode">状态</param>
        /// <returns></returns>
        public IEnumerable<T_SC_Page_ElementView> QueryPageElementByNameAndOwnerIdAndStateCode(string name, string ownerid, int statecode)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<T_SC_Page_ElementView>>(a =>
            {
                string sql = $@"select 
                                OwnerId,new_t_sc_page_elementId, CreatedOn, CreatedBy, 
                                ModifiedOn, ModifiedBy,  statecode, new_name 
                                from new_t_sc_page_elementBase where new_name='{name}' and ownerid='{ownerid}' and statecode={statecode} ";
                var sqlTemplate = new SqlBuilder().AddTemplate(sql);
                return a.Query<T_SC_Page_ElementView>(sqlTemplate.RawSql);
            });
        }

        #endregion

        /// <summary>
        /// 根据leadId获取筛查活动中的筛查项目
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> GetScreenItemData(Guid leadId)
        {
            return new DapperHelper().ExecueWithoutTransaction(select =>
            {
                string strsql = @"select distinct new_new_screenitem,new_new_screenitemName from new_sc_customer 
                                  left join new_screen on new_screen.new_screenId=new_sc_customer.new_scid 
                                      /**where**/ ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                sqlBuilder.Where("new_customerid = @leadId", new { leadId = leadId });
                sqlBuilder.Where("new_new_screenitem is not null");
                return select.Query<dynamic>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 按筛查组获取筛统计信息
        /// </summary>
        /// <param name="screenteamid">筛查组ID</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结果时间</param>
        /// <returns></returns>
        public ScreenStatisticsView QryScreenStatisticsBy(Guid screenteamid,string startDate,string endDate)
        {
            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = $@"select 
                                ISNULL(sum(s.new_totalinspectnumber),0)new_totalinspectnumber,
                                ISNULL(sum(s.new_cataractnumber),0)new_cataractnumber,
                                ISNULL(sum(s.new_glaucomanumber),0)new_glaucomanumber,
                                ISNULL(sum(s.new_indicationnumber),0)new_indicationnumber,
                                ISNULL(sum(s.new_reservations),0)new_reservations,
                                ISNULL(sum(s.new_arrived),0)new_arrived,
                                ISNULL(sum(s.new_signed),0)new_signed,
                                ISNULL(sum(s.new_operation),0)new_operation,
                                ISNULL(sum(s.new_unoperated),0)new_unoperated
                                from new_ec_screenpatients_situationbase s
                                where s.new_screengroup = '{screenteamid}' and s.new_starttime >= '{startDate}' and s.new_starttime <= '{endDate}'";

                var sqlTemplate = new SqlBuilder().AddTemplate(sql);
                return a.QueryFirstOrDefault<ScreenStatisticsView>(sqlTemplate.RawSql);
            });
        }
    }
}
